package packageDataAccess;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import javax.sql.DataSource;
import javax.naming.InitialContext;
import javax.naming.Context;
import javax.naming.NamingException;
import packageException.*;


/**
 *
 * @author Lionel & Julien
 */
class ReductionSyllathequeData {    
    /**
     * Retourne le nombre de réduction correspondant au syllabus passé en argument
     * 
     * @param syllabusId
     * @return nbReduction
     * @throws ReductionException
     */
    public int getNbReduction (int syllabusId) throws 
            ReductionException,
            DataException {
        int promo = 0;
        try {
            // Connexion à la base de données
            Context ctx = new InitialContext(); 
            DataSource source = (DataSource) ctx.lookup("jdbc/Syllatheque"); 
            Connection connexion = source.getConnection();

            // SQL
            String requete = "SELECT promotionId "
                    + "FROM reduction "
                    + "WHERE syllabusId = ? "
                    + "ORDER BY promotionId "
                    + "DESC";
            
            // Traitement
            PreparedStatement prepStat = connexion.prepareStatement(requete);
            prepStat.setInt(1, syllabusId);
            prepStat.setMaxRows(1);
            
            ResultSet donnees = prepStat.executeQuery();
            donnees.next();
            
            promo = donnees.getRow();

            // Fermeture de la connexion
            connexion.close();
            
        }
        catch(SQLException e) {
            // ERREUR
            throw new ReductionException(e.getMessage());
        }
        catch(NamingException e) {
            // ERREUR
            throw new DataException(e.getMessage());
        }
        return promo;
    }
    
    /**
     * Retourne un tableau de réduction.
     * 
     * @param syllabusId
     * @return ArrayList<Integer>
     * @throws ReductionException
     */
    public ArrayList<Integer> getBestReduction (int syllabusId) throws 
            ReductionException,
            DataException {
        ArrayList<Integer> promo = new ArrayList<Integer>();
        try {
            // Connexion à la base de données
            Context ctx = new InitialContext(); 
            DataSource source = (DataSource) ctx.lookup("jdbc/Syllatheque"); 
            Connection connexion = source.getConnection();

            // SQL
            String requete = "SELECT promotionId "
                    + "FROM reduction "
                    + "WHERE syllabusId = ? "
                    + "ORDER BY promotionId "
                    + "DESC";
            
            // Traitement
            PreparedStatement prepStat = connexion.prepareStatement(requete);
            prepStat.setInt(1, syllabusId);
            
            ResultSet donnees = prepStat.executeQuery();
            while(donnees.next()) {
                promo.add(donnees.getInt("promotionId"));
            }

            // Fermeture de la connexion
            connexion.close();
            
        }
        catch(SQLException e) {
            // ERREUR
            throw new ReductionException(e.getMessage());
        }
        catch(NamingException e) {
            // ERREUR
            throw new DataException(e.getMessage());
        }
        return promo;
    }
}
